package com.zygh.lz.dao;

import com.zygh.lz.entity.Xarea;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author wxh
 */
@Mapper
public interface IndexMapper {
    @Select(" select station ,count(1) as gwNum,COALESCE(sum(police)+sum(auxiliary),0) as bsjNum from xarea" +
            " where station not in ('九主','六块','示范区')    group by station " +
            " order by FIELD(station,'高峰岗','固定岗','网格','铁骑','机动岗','夜巡','高架岗','高速岗','九主','六块','示范区')")
    List<Map<String, Object>> station();

    @Select("<script>" +
            " select battalion ,count(1) as gwNum,sum(police) as mjNum,COALESCE(sum(auxiliary),0 )as fjNum from xarea xa " +
            " where station = #{name} " +
            " <if test='sectionId != null'>" +
            " and exists ( select 1 from section where sys_section_id = #{sectionId} and xa.section_Did = section_pid)" +
            "</if>" +
            " group by battalion " +
            "         order by\n" +
            "        FIELD(battalion,'郑州市公安局公安交通管理局','一支队','二支队','三支队','四支队','五支队','六支队','七支队','八支队','九支队','十支队','十一支队','十二支队','十三支队','高速公路交通警察一支队','高速公路交通警察二支队') " +
            "</script>")
    List<Map<String, Object>> battalion(@Param("name") String name, @Param("sectionId") Integer sectionId);

    @Select("<script> " +
            " select battalion ,count(1) as gwNum,COALESCE(sum(police) +sum(auxiliary),0) as bsjNum from xarea xa" +
            " <where> " +
            " whether = 1   " +
            " <if test='sectionId != null'>" +
            " and exists ( select 1 from section where sys_section_id = #{sectionId} and xa.section_Did = section_pid) " +
            "</if>" +
            " </where>" +
            "  group by battalion" +
            " order by FIELD(battalion,'郑州市公安局公安交通管理局','一支队','二支队','三支队','四支队','五支队','六支队','七支队','八支队','九支队','十支队','十一支队','十二支队','十三支队','高速公路交通警察一支队','高速公路交通警察二支队') " +
            "</script>")
    List<Map<String, Object>> indexBattalion(Integer sectionId);

    @Select("select station,count(1) as gwNum,sum(police) as mjNum,COALESCE(sum(auxiliary),0) as fjNum from xarea where battalion = #{name}  " +
            " group by station " +
            " order by FIELD(station,'高峰岗','固定岗','网格','铁骑','机动岗','夜巡','高架岗','高速岗','九主','六块','示范区')")
    List<Map<String, Object>> indexStation(@Param("name") String name);

    @Select(" select count(1) as stationCount from xarea where station = #{xarea.station} and battalion =#{xarea.battalion} ")
    Integer stationCount(@Param("xarea") Xarea xarea);

    @Select("select count(1) from xarea xa where station = #{xarea.station} and battalion = #{xarea.battalion} and exists (select 1 from sign sg where sg.assignmentid = xa.id and signintime like concat(#{today},'%'))")
    Integer signStationCount(@Param("xarea") Xarea xarea, @Param("today") String today);

    @Select("select sum(police)+sum(auxiliary) as policeNum from xarea xa where station = #{xarea.station} and battalion =#{xarea.battalion}")
    Integer policeNum(@Param("xarea") Xarea xarea);

    @Select("select count(1) as signPoliceNum from sign sg where signintime like concat(#{today},'%') and exists (select 1 from xarea xa where xa.id = sg.assignmentid and station = #{xarea.station} and battalion =#{xarea.battalion})")
    Integer signPoliceNum(@Param("xarea") Xarea xarea, @Param("today") String today);

    @Select("select id,name,gridding,station,(police+auxiliary ) as policeNum," +
            "(select name from duty_mode dm where dm.code =xa.pattern) as modeName from xarea xa " +
            "where station = #{xarea.station} and battalion =#{xarea.battalion}")
    List<Map<String, Object>> dataArr(@Param("xarea") Xarea xarea);

    @Select("select sg.warningsignal,sg.staffname,ss.duration,ss.mileage from (SELECT * FROM sign WHERE state = 1) sg left join" +
            " (SELECT * FROM sign_statistics WHERE sign_time LIKE  concat(#{today},'%')) ss " +
            " on sg.warningsignal = ss.warning_signal" +
            " where sg.assignmentid = #{id} GROUP BY sg.warningsignal")
    List<Map<String, Object>> detail(@Param("id") String id,
                                     @Param("today") String today);

    @Select(" select id,ssname,pid from sptype where yxzt = 1 ")
    List<Map<String, Object>> sptype();

    @Select(" select * from sign sg where comment = 2 and  sg.assignmentid = #{id} and signintime like concat(#{today},'%')")
    List<Map> sptypeDetail(@Param("id") String id,@Param("today") String today);

    @Select(" select count(1) from sign sg where comment = 2 and  sg.assignmentid = #{id} and signintime like concat(#{today},'%') group by assignmentid")
    Integer sign(@Param("id") String id,@Param("today") String today);
}

